package com.asiainfo.demo.dao.impl;

import com.asiainfo.demo.dao.idao.iBind;
import com.asiainfo.demo.dao.roles.Bind;

import java.sql.*;
import java.util.ArrayList;

public class BindImpl implements iBind {
    private Connection con = null;
    private PreparedStatement ps = null;

    public BindImpl(Connection con){
        this.con = con;
    }

    @Override
    public boolean insertBind(Bind bind) throws SQLException {
        String sql = "INSERT INTO MEM_BIND_CARD(MEM_ID, CARD_ID, BIND_VALID, TIMES, SIGN_TIME, LAST_TIME)" +
                        "VALUES (?, ?, ?, ?, ?, ?);";

        ps = con.prepareStatement(sql);
        ps.setInt(1, bind.getMemID());
        ps.setInt(2, bind.getCardID());
        ps.setInt(3, bind.getBindValid());
        ps.setInt(4, bind.getTimes());
        ps.setTimestamp(5, (Timestamp) bind.getSignTime());
        ps.setTimestamp(6, (Timestamp) bind.getLastTime());

        return ps.executeUpdate() > 0;
    }

    @Override
    public Bind findBindByBindID(int bindID) throws SQLException {
        String sql = "SELECT * FROM MEM_BIND_CARD WHERE BIND_ID=?;";
        Bind resBind = null;

        ps = con.prepareStatement(sql);
        ps.setInt(1, bindID);

        ResultSet resultSet = ps.executeQuery();
        if(resultSet.next()){
            resBind = new Bind();
            resBind.setBindID(bindID);
            resBind.setMemID(resultSet.getInt("MEM_ID"));
            resBind.setCardID(resultSet.getInt("CARD_ID"));
            resBind.setBindValid(resultSet.getByte("BIND_VALID"));
            resBind.setTimes(resultSet.getInt("TIMES"));
            resBind.setSignTime(resultSet.getTimestamp("SIGN_TIME"));
            resBind.setLastTime(resultSet.getTimestamp("LAST_TIME"));
        }

        return resBind;
    }

    @Override
    public ArrayList<Bind> findBindsByID(int ID, String type) throws SQLException{
        String sql = "SELECT * FROM MEM_BIND_CARD WHERE" + type + "_ID=?";

        ps = con.prepareStatement(sql);
        ps.setInt(1,ID);

        return resultSetToBind(ps);
    }

    @Override
    public ArrayList<Bind> findAllBind() throws SQLException {
        String sql = "SELECT * FROM MEM_BIND_CARD;";

        ps = con.prepareStatement(sql);

        return resultSetToBind(ps);
    }

    @Override
    public boolean changeValid(int bindID, byte newStatus) throws SQLException {
        String sql = "UPDATE MEM_BIND_CARD SET BIND_VALID = ? WHERE BIND_ID = ?;";

        ps = con.prepareStatement(sql);

        ps.setByte(1, newStatus);
        ps.setInt(2, bindID);

        return ps.executeUpdate() > 0;
    }

    private ArrayList<Bind> resultSetToBind(PreparedStatement ps) throws SQLException{
        ResultSet resultSet = ps.executeQuery();
        ArrayList<Bind> resBinds = new ArrayList<>();

        while (resultSet.next()){
            Bind resBind = new Bind();
            resBind.setBindID(resultSet.getInt("BIND_ID"));
            resBind.setMemID(resultSet.getInt("MEM_ID"));
            resBind.setCardID(resultSet.getInt("CARD_ID"));
            resBind.setBindValid(resultSet.getByte("BIND_VALID"));
            resBind.setTimes(resultSet.getInt("TIMES"));
            resBind.setSignTime(resultSet.getTimestamp("SIGN_TIME"));
            resBind.setLastTime(resultSet.getTimestamp("LAST_TIME"));

            resBinds.add(resBind);
        }

        return resBinds;
    }
}
